package org.free.todolist.data;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

import org.free.todolist.model.AppItem;

public class AppDataKit {
	private String message;

	private boolean status;

	public AppDataKit() {
		try {
			Class.forName("org.sqlite.JDBC");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	public boolean submitNewAppItem(AppItem app) {
		String query = "INSERT INTO apps (alias, exe, des) VALUES (?, ?, ?)";
		try {
			Connection con = DriverManager.getConnection("jdbc:sqlite:apps");
			PreparedStatement pstat = con.prepareStatement(query);
			pstat.setString(1, app.getAlias());
			pstat.setString(2, app.getExe());
			pstat.setString(3, app.getDes());

			pstat.execute();
			status = true;

			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
			message = e.getMessage();
			status = false;
		}
		return status;
	}

	public boolean updateExistAppItem(AppItem item) {
		String query = "UPDATE apps SET alias=?, exe=?, des=? WHERE id="
				+ item.getId();
		try {
			Connection con = DriverManager.getConnection("jdbc:sqlite:apps");
			PreparedStatement pstat = con.prepareStatement(query);
			pstat.setString(1, item.getAlias());
			pstat.setString(2, item.getExe());
			pstat.setString(3, item.getDes());

			pstat.execute();
			status = true;

			con.close();
		} catch (Exception e) {
			e.printStackTrace();
			message = e.getMessage();
			status = false;
		}

		return status;
	}

	public List<AppItem> searchList(String by, String value) {
		List<AppItem> list = new LinkedList<AppItem>();
		String query = "SELECT id, alias, des FROM apps WHERE " + by
				+ " LIKE \"" + value + "\"";
		try {
			Connection con = DriverManager.getConnection("jdbc:sqlite:apps");
			Statement stat = con.createStatement();
			ResultSet rs = stat.executeQuery(query);
			while (rs.next()) {
				AppItem item = new AppItem();
				item.setId(String.valueOf(rs.getInt("id")));
				item.setAlias(rs.getString("alias"));
				item.setDes(rs.getString("des"));

				list.add(item);
			}
			status = true;
		} catch (Exception e) {
			e.printStackTrace();
			message = e.getMessage();
			status = false;
		}

		return list;
	}

	public String getMessage() {
		return message;
	}

}
